﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.OleDb;
using System.IO;

namespace HotelProject
{
    public class MySQLDatabaseConnector : IDatabase
    {
        /// <summary>
        /// The Database - ADO Entity
        /// </summary>
        bshrews2Entities dataBase;
        

        /// <summary>
        /// Creates the database object and connects to the MySQL 
        /// </summary>
        /// <param name="pathToDB"></param>
        public MySQLDatabaseConnector()
        {
            connect();
      
        }
    
        /// <summary>
        /// Connects to the DB
        /// </summary>
        /// <returns></returns>
        private bool connect()
        {
            dataBase = new bshrews2Entities();
            return true;
        }

        /// <summary>
        /// Disconnects from the DB
        /// </summary>
        /// <returns></returns>
        public bool disconnect()
        {
            dataBase.Dispose();
            return true;
        }

        /// <summary>
        /// push the lcoal changes to the DB
        /// </summary>
        /// <returns></returns>
        public bool saveChanges()
        {
            dataBase.SaveChanges();
            return true;
        }

        /// <summary>
        /// Returns the current bookings between the specified dates
        /// </summary>
        /// <param name="startDate"></param>
        /// <param name="sendDate"></param>
        /// <returns></returns>
        public Booking[] getBookingsBetweenDates(DateTime startDate, DateTime endDate)
        {

            var bookingQuery = from b in dataBase.bookings
                               where (b.startDate > startDate && b.endDate >= startDate) || (b.endDate < endDate && b.endDate >= startDate)
                               orderby b.startDate
                               select b;
            

            return bookingQuery.ToArray<Booking>();
        }

        public String[] getViews()
        {
            var views = from b in dataBase.hotelRooms
                               select b.view;
            return views.Distinct<String>().ToArray<String>();
        }


        public String[] getBedSizes()
        {
            var views = from b in dataBase.hotelRooms
                        select b.bedSize;
            return views.Distinct<String>().ToArray<String>();
        }

        public int[] getPrices()
        {
            var views = from b in dataBase.hotelRooms 
                        select b.price;
            
            decimal[] prices = views.Distinct<decimal>().ToArray<decimal>();

            int[] arrayOfPrices = new int[prices.Length];
            
            for(int i = 0; i < prices.Length; i++)
            {
                arrayOfPrices[i] = (int) prices[i];
            }
            Array.Sort(arrayOfPrices);

            return arrayOfPrices;
        }

        public int[] getFloors()
        {
            var views = from b in dataBase.hotelRooms
                        select b.floorNumber;

            int[] floors = views.Distinct<int>().ToArray<int>();

            Array.Sort(floors);

            return floors;
        }

        public int[] getRoomNumbers()
        {
            var views = from b in dataBase.hotelRooms
                        select b.roomNumber;

            int[] roomNumbers = views.Distinct<int>().ToArray<int>();

            Array.Sort(roomNumbers);

            return roomNumbers;
        }

        /// <summary>
        /// Adds the new booking to the local copy of the database
        /// Must call saveChanges to push the changes to the actual database
        /// </summary>
        /// <param name="newBooking">The Booking to Add</param>
        public void addBooking(Booking newBooking)
        {
            dataBase.AddTobookings(newBooking);
        }

        /// <summary>
        /// Remove a booking from the local copy of the database
        /// Must call saveChanges to push the changes to the actual database
        /// </summary>
        /// <param name="userToRemove">The booking to remove</param>
        public void removeBooking(Booking bookingToRemove)
        {
            dataBase.bookings.DeleteObject(bookingToRemove);
        }

        /// <summary>
        /// Get the first booking matching the parameter 
        /// </summary>
        /// <param name="searchField">the parameter to search</param>
        /// <param name="searchCriteria">the search criteria</param>
        /// <returns>the first booking found</returns>
        public Booking[] getBookings()
        {
            var bookingQuery = from b in dataBase.bookings
                               orderby b.startDate
                               select b;

            return bookingQuery.ToArray<Booking>();
           
            
        }

        /// <summary>
        /// Get all the bookings matching the parameter 
        /// </summary>
        /// <param name="searchField">the parameter to search</param>
        /// <param name="searchCriteria">the search criteria</param>
        /// <returns>the bookings found</returns>
        public Booking[] getBookingsByUser(String userName)
        {
            var bookingQuery = from b in dataBase.bookings

                               where b.userName == userName
                               orderby b.startDate
                               select b;

            return bookingQuery.ToArray<Booking>();
        }

        /// <summary>
        /// Get all the bookings matching the parameter 
        /// </summary>
        /// <param name="searchField">the parameter to search</param>
        /// <param name="searchCriteria">the search criteria</param>
        /// <returns>the bookings found</returns>
        public Booking getBookingsByID(int roomID)
        {
            var bookingQuery = from b in dataBase.bookings

                               where b.ID == roomID
                               select b;

            return bookingQuery.ToArray<Booking>()[0];
        }

        /// <summary>
        /// Add a new user to the local db
        /// Must call saveChanges to push the changes to the actual database
        /// </summary>
        /// <param name="newUser">The user to add</param>
        public void addUser(registeredUser newUser)
        {
            dataBase.AddToregisteredUsers(newUser);
        }

        public void addHotel(hotel newHotel)
        {
            dataBase.AddTohotels(newHotel);
        }

        public void addHotelRoom(hotelRoom newRoom)
        {
            dataBase.AddTohotelRooms(newRoom);
        }

        

        /// <summary>
        /// Remove a user from the local copy of the database
        /// Must call saveChanges to push the changes to the actual database
        /// </summary>
        /// <param name="userToRemove"></param>
        public void removeUser(registeredUser userToRemove)
        {
            dataBase.registeredUsers.DeleteObject(userToRemove);
        }

        public hotelRoom GetRoom(int ID)
        {
            var userQuery = from u in dataBase.hotelRooms
                            where u.roomID == ID
                            select u;
            return userQuery.ToArray<hotelRoom>()[0];
        }
        
        /// <summary>
        /// Get all users found based on the provided search criteria
        /// </summary>
        /// <param name="searchField">the parameter to search in</param>
        /// <param name="searchCriteria">the criteria to search for</param>
        /// <returns>all the users found based on the criteria</returns>
        public registeredUser getUser(String userName)
        {

            var userQuery = from u in dataBase.registeredUsers
                                where u.userName == userName
                                orderby u.userName
                                select u;
            return userQuery.ToArray<registeredUser>()[0];
        }

        public registeredUser getPassword(String password)
        {
            var userQuery = from u in dataBase.registeredUsers
                            where u.userPassword == password
                            orderby u.userName
                            select u;
            return userQuery.ToArray<registeredUser>()[0];

        }

        public bool Login(String username, String password)
        {
            registeredUser toCheck = this.getUser(username);
            if (toCheck.userPassword.Equals(password))
            {
                return true;
            }

            return false;
        }

        /// <summary>
        /// returns all hotel rooms in all hotels
        /// </summary>
        /// <returns>all hotel rooms</returns>
        public hotelRoom[] returnHotelRooms()
        {
            var roomQuery = from r in dataBase.hotelRooms
                            orderby r.roomID
                            select r;

            return roomQuery.ToArray<hotelRoom>();
        }

        public hotel[] getHotels()
        {
            var roomQuery = from r in dataBase.hotels
                            orderby r.hotelName
                            select r;

            return roomQuery.ToArray<hotel>();
        }

        /// <summary>
        /// returns all available hotel rooms that are vacant within the time specified
        /// </summary>0
        /// <param name="startDate">The beginning of a guest's desired stay</param>
        /// <param name="endDate">The end of a guests desired stay</param>
        /// <returns>all available rooms within the date</returns>
        public hotelRoom[] getAvailableHotelRoomsByDate(DateTime startDate, DateTime endDate)
        {
            List<hotelRoom> allRooms = returnHotelRooms().ToList<hotelRoom>();
            Booking[] datesBooked = getBookingsBetweenDates(startDate, endDate);

            if (DateTime.Compare(startDate, endDate) > 0)
            {
                throw new Exception("Terrible date.  I hope you die.");
            }

            List<hotelRoom> roomsToRemove = new List<hotelRoom>();

            foreach (Booking b in datesBooked)
            {
                foreach (hotelRoom h in allRooms)
                {
                    if (b.roomID == h.roomID)
                    {
                        roomsToRemove.Add(h);
                    }
                }
            }

            foreach (hotelRoom room in roomsToRemove)
            {
                allRooms.Remove(room);
            }

            return allRooms.ToArray<hotelRoom>();
  
        }

        /// <summary>
        /// returns all available hotel rooms that are vacant within the time specified and at the specified hotel
        /// If hotelId equals 0, returns all rooms in all hotels
        /// </summary>
        /// <param name="startDate">The beginning of a guest's desired stay</param>
        /// <param name="endDate">The end of a guests desired stay</param>
        /// <returns>all available rooms within the date</returns>
        public hotelRoom[] getAvailableHotelRoomsByDate(DateTime startDate, DateTime endDate, int hotelId)
        {
            List<hotelRoom> roomsToRemove = new List<hotelRoom>();
            List<hotelRoom> allRooms= getAvailableHotelRoomsByDate(startDate, endDate).ToList<hotelRoom>();

            if (hotelId != 0)
            {
                foreach (hotelRoom h in allRooms)
                {
                    if (h.hotelID != hotelId)
                    {
                        roomsToRemove.Add(h);
                    }
                }

                foreach (hotelRoom room in roomsToRemove)
                {
                    allRooms.Remove(room);
                }
            }

            return allRooms.ToArray<hotelRoom>();
        }

        public hotel GetHotel(int ID)
        {
            var hotelQuery = from r in dataBase.hotels
                            where r.hotelID == ID
                            select r;

            return hotelQuery.ToArray<hotel>()[0];
        }

        public String getHotelName(int ID)
        {
            return GetHotel(ID).hotelName;
        }

    }
}